USE [QLTX]
GO
/****** Object:  Table [dbo].[HangSanXuat]    Script Date: 06/23/2011 09:35:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HangSanXuat](
	[MaHang] [int] IDENTITY(1,1) NOT NULL,
	[TenHang] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[QuocGia] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[WebSite] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED 
(
	[MaHang] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
USE [QLTX]
GO
/****** Object:  Table [dbo].[HinhThucThanhToan]    Script Date: 06/23/2011 09:36:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HinhThucThanhToan](
	[MaHT] [int] IDENTITY(1,1) NOT NULL,
	[TenHT] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED 
(
	[MaHT] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
USE [QLTX]
GO
/****** Object:  Table [dbo].[HinhThucThue]    Script Date: 06/23/2011 09:36:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HinhThucThue](
	[MaHT] [int] IDENTITY(1,1) NOT NULL,
	[TenHT] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[MaHT] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
USE [QLTX]
GO
/****** Object:  Table [dbo].[KhachHang]    Script Date: 06/23/2011 09:36:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KhachHang](
	[MaKH] [int] IDENTITY(1,1) NOT NULL,
	[TenKH] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[DiaChi] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DienThoai] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CMND] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[MaKH] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
USE [QLTX]
GO
/****** Object:  Table [dbo].[LoaiXe]    Script Date: 06/23/2011 09:36:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LoaiXe](
	[MaLoai] [int] IDENTITY(1,1) NOT NULL,
	[TenLoai] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[MaLoai] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
USE [QLTX]
GO
/****** Object:  Table [dbo].[MauXe]    Script Date: 06/23/2011 09:36:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MauXe](
	[MaMau] [int] IDENTITY(1,1) NOT NULL,
	[TenMau] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED 
(
	[MaMau] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
USE [QLTX]
GO
/****** Object:  Table [dbo].[NhaCungCap]    Script Date: 06/23/2011 09:36:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NhaCungCap](
	[MaNCC] [int] IDENTITY(1,1) NOT NULL,
	[TenNCC] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[DiaChi] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[DienThoai] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[MaNCC] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
USE [QLTX]
GO
/****** Object:  Table [dbo].[NhanVien]    Script Date: 06/23/2011 09:36:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NhanVien](
	[MaNV] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[TenNV] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[DiaChi] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[DienThoai] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SoCMND] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[MatKhau] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Quyen] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[MaNV] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
USE [QLTX]
GO
/****** Object:  Table [dbo].[TinhTrangXe]    Script Date: 06/23/2011 09:36:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TinhTrangXe](
	[MaTT] [int] IDENTITY(1,1) NOT NULL,
	[MoTa] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED 
(
	[MaTT] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
USE [QLTX]
GO
/****** Object:  Table [dbo].[Xe]    Script Date: 06/23/2011 09:37:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Xe](
	[BienSo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[TenXe] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Gia] [money] NULL,
	[NamSX] [int] NULL,
	[MaNCC] [int] NOT NULL,
	[MaLoai] [int] NULL,
	[MaHang] [int] NULL,
	[MaMau] [int] NULL,
	[MaTT] [int] NULL,
	[ThoiGianDoi] [datetime] NULL,
	[Ranh] [bit] NOT NULL CONSTRAINT [DF_Xe_Ranh]  DEFAULT ((1)),
PRIMARY KEY CLUSTERED 
(
	[BienSo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Xe]  WITH CHECK ADD FOREIGN KEY([MaHang])
REFERENCES [dbo].[HangSanXuat] ([MaHang])
GO
ALTER TABLE [dbo].[Xe]  WITH CHECK ADD FOREIGN KEY([MaLoai])
REFERENCES [dbo].[LoaiXe] ([MaLoai])
GO
ALTER TABLE [dbo].[Xe]  WITH CHECK ADD FOREIGN KEY([MaMau])
REFERENCES [dbo].[MauXe] ([MaMau])
GO
ALTER TABLE [dbo].[Xe]  WITH CHECK ADD FOREIGN KEY([MaNCC])
REFERENCES [dbo].[NhaCungCap] ([MaNCC])
GO
ALTER TABLE [dbo].[Xe]  WITH CHECK ADD FOREIGN KEY([MaTT])
REFERENCES [dbo].[TinhTrangXe] ([MaTT])
USE [QLTX]
GO
USE [QLTX]
GO
/****** Object:  Table [dbo].[PhieuMuon]    Script Date: 06/23/2011 10:15:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[PhieuMuon](
	[MaPhieu] [int] IDENTITY(1,1) NOT NULL,
	[MaKH] [int] NOT NULL,
	[NgayTao] [datetime] NOT NULL,
	[MaNV] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ThoiGianDoi] [datetime] NULL,
	[TongTien] [float] NOT NULL CONSTRAINT [DF_PhieuMuon_TongTien]  DEFAULT ((0)),
	[MaHT] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[MaPhieu] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PhieuMuon]  WITH CHECK ADD FOREIGN KEY([MaKH])
REFERENCES [dbo].[KhachHang] ([MaKH])
GO
ALTER TABLE [dbo].[PhieuMuon]  WITH CHECK ADD FOREIGN KEY([MaNV])
REFERENCES [dbo].[NhanVien] ([MaNV])
GO
ALTER TABLE [dbo].[PhieuMuon]  WITH CHECK ADD  CONSTRAINT [FK_PhieuMuon_HinhThucThanhToan] FOREIGN KEY([MaHT])
REFERENCES [dbo].[HinhThucThanhToan] ([MaHT])
GO
ALTER TABLE [dbo].[PhieuMuon] CHECK CONSTRAINT [FK_PhieuMuon_HinhThucThanhToan]
/****** Object:  Table [dbo].[PhieuPhat]    Script Date: 06/23/2011 09:38:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[PhieuPhat](
	[MaPhieu] [int] NOT NULL,
	[MaKH] [int] NULL,
	[NgayLap] [datetime] NULL,
	[MaNV] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED 
(
	[MaPhieu] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PhieuPhat]  WITH CHECK ADD FOREIGN KEY([MaKH])
REFERENCES [dbo].[KhachHang] ([MaKH])
GO
ALTER TABLE [dbo].[PhieuPhat]  WITH CHECK ADD FOREIGN KEY([MaNV])
REFERENCES [dbo].[NhanVien] ([MaNV])
USE [QLTX]
GO
USE [QLTX]
GO
/****** Object:  Table [dbo].[ChiTietPhieuMuon]    Script Date: 06/23/2011 10:16:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ChiTietPhieuMuon](
	[MaPhieu] [int] NOT NULL,
	[BienSo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[TienDat] [money] NOT NULL,
	[TienThue] [money] NOT NULL,
	[NgayThue] [datetime] NOT NULL,
	[NgayHenTra] [datetime] NOT NULL,
	[GhiChu] [nvarchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[NgayTra] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[MaPhieu] ASC,
	[BienSo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ChiTietPhieuMuon]  WITH CHECK ADD FOREIGN KEY([BienSo])
REFERENCES [dbo].[Xe] ([BienSo])
GO
ALTER TABLE [dbo].[ChiTietPhieuMuon]  WITH CHECK ADD FOREIGN KEY([MaPhieu])
REFERENCES [dbo].[PhieuMuon] ([MaPhieu])
/****** Object:  Table [dbo].[ChiTietPhieuPhat]    Script Date: 06/23/2011 09:38:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChiTietPhieuPhat](
	[MaPhieu] [int] NULL,
	[LyDoPhat] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[ChiTietPhieuPhat]  WITH CHECK ADD FOREIGN KEY([MaPhieu])
REFERENCES [dbo].[PhieuPhat] ([MaPhieu])
USE [QLTX]
GO
/****** Object:  Table [dbo].[GiaThue]    Script Date: 06/23/2011 09:38:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[GiaThue](
	[BienSo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[MaHT] [int] NOT NULL,
	[DonGia] [money] NULL,
PRIMARY KEY CLUSTERED 
(
	[BienSo] ASC,
	[MaHT] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[GiaThue]  WITH CHECK ADD FOREIGN KEY([BienSo])
REFERENCES [dbo].[Xe] ([BienSo])
GO
ALTER TABLE [dbo].[GiaThue]  WITH CHECK ADD FOREIGN KEY([MaHT])
REFERENCES [dbo].[HinhThucThue] ([MaHT])

-- Trigger




USE [QLTX]
GO
/****** Object:  Trigger [dbo].[CTPM_insert]    Script Date: 06/23/2011 09:41:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE trigger [dbo].[CTPM_insert] 
on [dbo].[ChiTietPhieuMuon]
for insert 
as 
	declare @bienso varchar(20)
	declare @giathue money
	declare @maht int
	declare @maphieu int
	select @maphieu=maphieu,@bienso=bienso,@maht=maht from inserted
	select @giathue=dongia from giathue where bienso = @bienso and maht=@maht
	update chitietphieumuon set tienthue=@giathue where maphieu=@maphieu and bienso=@bienso
	update phieumuon set tongtien=tongtien+@giathue where maphieu = @maphieu
	update xe set ranh=0

